cls
clear all
set matsize 11000

********************************************************************************

**** Industry: Transportation and Utilities ************************************
	
	*-> 1969 - 2000
	
	foreach ind of newlist transutil {
	
	clear all
	
	foreach var of newlist pay emp {
		
		clear all
	
		** BEA, 1969-1970
			
			qui import delimited "raw/BEA/`ind'`var'_1969_1970.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4) (fips `var'1969 `var'1970)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4) (fips `var'1969 `var'1970)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1969_1970.dta",replace
		
		** BEA, 1971-1975
			
			qui import delimited "raw/BEA/`ind'`var'_1971_1975.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1971 `var'1972 `var'1973 `var'1974 `var'1975)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1971 `var'1972 `var'1973 `var'1974 `var'1975)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1971_1975.dta",replace
		
		** BEA, 1976-1980
			
			qui import delimited "raw/BEA/`ind'`var'_1976_1980.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1976 `var'1977 `var'1978 `var'1979 `var'1980)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1976 `var'1977 `var'1978 `var'1979 `var'1980)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1976_1980.dta",replace
		
		** BEA, 1981-1985
			
			qui import delimited "raw/BEA/`ind'`var'_1981_1985.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1981 `var'1982 `var'1983 `var'1984 `var'1985)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1981 `var'1982 `var'1983 `var'1984 `var'1985)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1981_1985.dta",replace

		** BEA, 1986-1990
			
			qui import delimited "raw/BEA/`ind'`var'_1986_1990.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1986 `var'1987 `var'1988 `var'1989 `var'1990)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1986 `var'1987 `var'1988 `var'1989 `var'1990)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1986_1990.dta",replace
			
		** BEA, 1991-1995
			
			qui import delimited "raw/BEA/`ind'`var'_1991_1995.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1991 `var'1992 `var'1993 `var'1994 `var'1995)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1991 `var'1992 `var'1993 `var'1994 `var'1995)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1991_1995.dta",replace
			
		** BEA, 1996-2000
			
			qui import delimited "raw/BEA/`ind'`var'_1996_2000.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1996 `var'1997 `var'1998 `var'1999 `var'2000)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1996 `var'1997 `var'1998 `var'1999 `var'2000)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1996_2000.dta",replace
			
			clear all
			
		** Append
		
			clear
			append using "temp/temp_1969_1970.dta"
			rm "temp/temp_1969_1970.dta"
			append using "temp/temp_1971_1975.dta"
			rm "temp/temp_1971_1975.dta"
			append using "temp/temp_1976_1980.dta"
			rm "temp/temp_1976_1980.dta"
			append using "temp/temp_1981_1985.dta"
			rm "temp/temp_1981_1985.dta"
			append using "temp/temp_1986_1990.dta"
			rm "temp/temp_1986_1990.dta"
			append using "temp/temp_1991_1995.dta"
			rm "temp/temp_1991_1995.dta"
			append using "temp/temp_1996_2000.dta"
			rm "temp/temp_1996_2000.dta"

		** Drop Alaska and Hawaii

			qui g stfips = int(fips/1000)
			qui drop if stfips==2 | stfips==15

		** Fix Counties

			qui drop if fips==55901
			qui replace fips = 46113 if fips==46102
			qui replace fips = 51901 if fips==51003|fips==51540
			qui replace fips = 51903 if fips==51005|fips==51580
			qui replace fips = 51907 if fips==51015|fips==51790|fips==51820
			qui replace fips = 51909 if fips==51019|fips==51515
			qui replace fips = 51911 if fips==51031|fips==51680
			qui replace fips = 51913 if fips==51035|fips==51640
			qui replace fips = 51918 if fips==51053|fips==51570|fips==51730
			qui replace fips = 51919 if fips==51059|fips==51600|fips==51610
			qui replace fips = 51921 if fips==51069|fips==51840
			qui replace fips = 51923 if fips==51081|fips==51595
			qui replace fips = 51929 if fips==51089|fips==51690
			qui replace fips = 51931 if fips==51095|fips==51830
			qui replace fips = 51933 if fips==51121|fips==51750
			qui replace fips = 51939 if fips==51143|fips==51590
			qui replace fips = 51941 if fips==51149|fips==51670
			qui replace fips = 51942 if fips==51153|fips==51683|fips==51685
			qui replace fips = 51944 if fips==51161|fips==51775
			qui replace fips = 51945 if fips==51163|fips==51530|fips==51678
			qui replace fips = 51947 if fips==51165|fips==51660
			qui replace fips = 51949 if fips==51175|fips==51620
			qui replace fips = 51951 if fips==51177|fips==51630
			qui replace fips = 51953 if fips==51191|fips==51520
			qui replace fips = 51955 if fips==51195|fips==51720
			qui replace fips = 51958 if fips==51199|fips==51735

			qui replace `var' = 0 if `var'<0
			collapse (sum) `var', by(year fips) 
			
			qui save "data/BEA/temp_`ind'`var'_1969_2000.dta",replace
			
			}
			
			qui use "data/BEA/temp_`ind'emp_1969_2000.dta", clear
			qui merge 1:1 fips year using "data/BEA/temp_`ind'pay_1969_2000.dta"
			qui save "data/BEA/temp_`ind'_1969_2000.dta",replace
			rm "data/BEA/temp_`ind'emp_1969_2000.dta"
			rm "data/BEA/temp_`ind'pay_1969_2000.dta"
			
			}
		
	*-> 2001 - 2018
	
	foreach ind of newlist trans utilities information {
	
	clear all
	
	foreach var of newlist pay emp {
		
		clear all
			
		** BEA, 2001-2005
			
			qui import delimited "raw/BEA/`ind'`var'_2001_2005.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2001 `var'2002 `var'2003 `var'2004 `var'2005)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2001 `var'2002 `var'2003 `var'2004 `var'2005)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2001_2005.dta",replace

		** BEA, 2006-2010
			
			qui import delimited "raw/BEA/`ind'`var'_2006_2010.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2006 `var'2007 `var'2008 `var'2009 `var'2010)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2006 `var'2007 `var'2008 `var'2009 `var'2010)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2006_2010.dta",replace

		** BEA, 2011-2015
			
			qui import delimited "raw/BEA/`ind'`var'_2011_2015.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2011 `var'2012 `var'2013 `var'2014 `var'2015)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2011 `var'2012 `var'2013 `var'2014 `var'2015)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2011_2015.dta",replace

		** BEA, 2016-2018
			
			qui import delimited "raw/BEA/`ind'`var'_2016_2018.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5) (fips `var'2017 `var'2016 `var'2018)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5) (fips `var'2017 `var'2016 `var'2018)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2016_2018.dta",replace
			
			clear all
			
		** Append
		
			clear
			append using "temp/temp_2001_2005.dta"
			rm "temp/temp_2001_2005.dta"
			append using "temp/temp_2006_2010.dta"
			rm "temp/temp_2006_2010.dta"
			append using "temp/temp_2011_2015.dta"
			rm "temp/temp_2011_2015.dta"
			append using "temp/temp_2016_2018.dta"
			rm "temp/temp_2016_2018.dta"

		** Drop Alaska and Hawaii

			qui g stfips = int(fips/1000)
			drop if stfips==2 | stfips==15

		** Fix Counties

			qui drop if fips==55901
			qui replace fips = 46113 if fips==46102
			qui replace fips = 51901 if fips==51003|fips==51540
			qui replace fips = 51903 if fips==51005|fips==51580
			qui replace fips = 51907 if fips==51015|fips==51790|fips==51820
			qui replace fips = 51909 if fips==51019|fips==51515
			qui replace fips = 51911 if fips==51031|fips==51680
			qui replace fips = 51913 if fips==51035|fips==51640
			qui replace fips = 51918 if fips==51053|fips==51570|fips==51730
			qui replace fips = 51919 if fips==51059|fips==51600|fips==51610
			qui replace fips = 51921 if fips==51069|fips==51840
			qui replace fips = 51923 if fips==51081|fips==51595
			qui replace fips = 51929 if fips==51089|fips==51690
			qui replace fips = 51931 if fips==51095|fips==51830
			qui replace fips = 51933 if fips==51121|fips==51750
			qui replace fips = 51939 if fips==51143|fips==51590
			qui replace fips = 51941 if fips==51149|fips==51670
			qui replace fips = 51942 if fips==51153|fips==51683|fips==51685
			qui replace fips = 51944 if fips==51161|fips==51775
			qui replace fips = 51945 if fips==51163|fips==51530|fips==51678
			qui replace fips = 51947 if fips==51165|fips==51660
			qui replace fips = 51949 if fips==51175|fips==51620
			qui replace fips = 51951 if fips==51177|fips==51630
			qui replace fips = 51953 if fips==51191|fips==51520
			qui replace fips = 51955 if fips==51195|fips==51720
			qui replace fips = 51958 if fips==51199|fips==51735

			qui replace `var' = 0 if `var'<0
			collapse (sum) `var', by(year fips) 
			
			save "data/BEA/temp_`ind'`var'_2001_2018.dta",replace
			
			}
			
			qui use "data/BEA/temp_`ind'emp_2001_2018.dta", clear
			qui merge 1:1 fips year using "data/BEA/temp_`ind'pay_2001_2018.dta"
			qui save "data/BEA/temp_`ind'_2001_2018.dta",replace
			rm "data/BEA/temp_`ind'emp_2001_2018.dta"
			rm "data/BEA/temp_`ind'pay_2001_2018.dta"
			
			}
	
	*-> Combine Industries, Transportation and Utilities for 2001-2018
	
		clear
		qui append using "data/BEA/temp_trans_2001_2018.dta"
		qui append using "data/BEA/temp_utilities_2001_2018.dta"
		qui append using "data/BEA/temp_information_2001_2018.dta"
		collapse (sum) pay emp, by(fips year)
		qui save "data/BEA/temp_transutil_2001_2018.dta", replace
		rm "data/BEA/temp_trans_2001_2018.dta"
		rm "data/BEA/temp_utilities_2001_2018.dta"
		rm "data/BEA/temp_information_2001_2018.dta"
		
	*-> Combine Years, 1969-200 and 2001-2018
	
		clear
		qui append using "data/BEA/temp_transutil_1969_2000.dta"
		qui append using "data/BEA/temp_transutil_2001_2018.dta"
		qui g industry = "transutil"
		keeporder ind* year fips pay emp 
		sort year fips
		qui save "data/BEA/temp_transutil_1969_2018.dta", replace
		rm "data/BEA/temp_transutil_1969_2000.dta"
		rm "data/BEA/temp_transutil_2001_2018.dta"
		
**** Industry: All Non-Farm, Manufacturing, Mining *****************************
	
	
	foreach ind of newlist nonfarm mfg mining {
	
	clear all
	
	foreach var of newlist emp pay {
		
		clear all
	
		** BEA, 1969-1970
			
			qui import delimited "raw/BEA/`ind'`var'_1969_1970.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4) (fips `var'1969 `var'1970)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4) (fips `var'1969 `var'1970)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1969_1970.dta",replace
		
		** BEA, 1971-1975
			
			qui import delimited "raw/BEA/`ind'`var'_1971_1975.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1971 `var'1972 `var'1973 `var'1974 `var'1975)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1971 `var'1972 `var'1973 `var'1974 `var'1975)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1971_1975.dta",replace
		
		** BEA, 1976-1980
			
			qui import delimited "raw/BEA/`ind'`var'_1976_1980.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1976 `var'1977 `var'1978 `var'1979 `var'1980)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1976 `var'1977 `var'1978 `var'1979 `var'1980)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1976_1980.dta",replace
		
		** BEA, 1981-1985
			
			qui import delimited "raw/BEA/`ind'`var'_1981_1985.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1981 `var'1982 `var'1983 `var'1984 `var'1985)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1981 `var'1982 `var'1983 `var'1984 `var'1985)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1981_1985.dta",replace

		** BEA, 1986-1990
			
			qui import delimited "raw/BEA/`ind'`var'_1986_1990.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1986 `var'1987 `var'1988 `var'1989 `var'1990)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1986 `var'1987 `var'1988 `var'1989 `var'1990)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1986_1990.dta",replace
			
		** BEA, 1991-1995
			
			qui import delimited "raw/BEA/`ind'`var'_1991_1995.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1991 `var'1992 `var'1993 `var'1994 `var'1995)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1991 `var'1992 `var'1993 `var'1994 `var'1995)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1991_1995.dta",replace
			
		** BEA, 1996-2000
			
			qui import delimited "raw/BEA/`ind'`var'_1996_2000.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5spersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'1996 `var'1997 `var'1998 `var'1999 `var'2000)
				}
			if "`var'" == "emp" {
				rename (caemp25stotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'1996 `var'1997 `var'1998 `var'1999 `var'2000)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_1996_2000.dta",replace

		** BEA, 2001-2005
			
			qui import delimited "raw/BEA/`ind'`var'_2001_2005.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2001 `var'2002 `var'2003 `var'2004 `var'2005)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2001 `var'2002 `var'2003 `var'2004 `var'2005)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2001_2005.dta",replace

		** BEA, 2006-2010
			
			qui import delimited "raw/BEA/`ind'`var'_2006_2010.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2006 `var'2007 `var'2008 `var'2009 `var'2010)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2006 `var'2007 `var'2008 `var'2009 `var'2010)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2006_2010.dta",replace

		** BEA, 2011-2015
			
			qui import delimited "raw/BEA/`ind'`var'_2011_2015.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5 v6 v7) (fips `var'2011 `var'2012 `var'2013 `var'2014 `var'2015)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5 v6 v7) (fips `var'2011 `var'2012 `var'2013 `var'2014 `var'2015)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2011_2015.dta",replace

		** BEA, 2016-2018
			
			qui import delimited "raw/BEA/`ind'`var'_2016_2018.csv", rowrange(6:3143) clear
			if "`var'" == "pay" {
				rename (cainc5npersonalincomebymajorcomp v3 v4 v5) (fips `var'2017 `var'2016 `var'2018)
				}
			if "`var'" == "emp" {
				rename (caemp25ntotalfulltimeandparttime v3 v4 v5) (fips `var'2017 `var'2016 `var'2018)
				}
			qui reshape long `var',i(fips) j(year)
			drop v2
			qui replace `var' = "0" if `var'=="(NA)" | `var'=="(D)"
			qui destring `var', replace
			qui save "temp/temp_2016_2018.dta",replace
			
			clear all
			
		** Append
		
			clear
			append using "temp/temp_1969_1970.dta"
			rm "temp/temp_1969_1970.dta"
			append using "temp/temp_1971_1975.dta"
			rm "temp/temp_1971_1975.dta"
			append using "temp/temp_1976_1980.dta"
			rm "temp/temp_1976_1980.dta"
			append using "temp/temp_1981_1985.dta"
			rm "temp/temp_1981_1985.dta"
			append using "temp/temp_1986_1990.dta"
			rm "temp/temp_1986_1990.dta"
			append using "temp/temp_1991_1995.dta"
			rm "temp/temp_1991_1995.dta"
			append using "temp/temp_1996_2000.dta"
			rm "temp/temp_1996_2000.dta"
			append using "temp/temp_2001_2005.dta"
			rm "temp/temp_2001_2005.dta"
			append using "temp/temp_2006_2010.dta"
			rm "temp/temp_2006_2010.dta"
			append using "temp/temp_2011_2015.dta"
			rm "temp/temp_2011_2015.dta"
			append using "temp/temp_2016_2018.dta"
			rm "temp/temp_2016_2018.dta"

		** Drop Alaska and Hawaii

			qui g stfips = int(fips/1000)
			drop if stfips==2 | stfips==15

		** Fix Counties

			qui drop if fips==55901
			qui replace fips = 46113 if fips==46102
			qui replace fips = 51901 if fips==51003|fips==51540
			qui replace fips = 51903 if fips==51005|fips==51580
			qui replace fips = 51907 if fips==51015|fips==51790|fips==51820
			qui replace fips = 51909 if fips==51019|fips==51515
			qui replace fips = 51911 if fips==51031|fips==51680
			qui replace fips = 51913 if fips==51035|fips==51640
			qui replace fips = 51918 if fips==51053|fips==51570|fips==51730
			qui replace fips = 51919 if fips==51059|fips==51600|fips==51610
			qui replace fips = 51921 if fips==51069|fips==51840
			qui replace fips = 51923 if fips==51081|fips==51595
			qui replace fips = 51929 if fips==51089|fips==51690
			qui replace fips = 51931 if fips==51095|fips==51830
			qui replace fips = 51933 if fips==51121|fips==51750
			qui replace fips = 51939 if fips==51143|fips==51590
			qui replace fips = 51941 if fips==51149|fips==51670
			qui replace fips = 51942 if fips==51153|fips==51683|fips==51685
			qui replace fips = 51944 if fips==51161|fips==51775
			qui replace fips = 51945 if fips==51163|fips==51530|fips==51678
			qui replace fips = 51947 if fips==51165|fips==51660
			qui replace fips = 51949 if fips==51175|fips==51620
			qui replace fips = 51951 if fips==51177|fips==51630
			qui replace fips = 51953 if fips==51191|fips==51520
			qui replace fips = 51955 if fips==51195|fips==51720
			qui replace fips = 51958 if fips==51199|fips==51735

			qui replace `var' = 0 if `var'<0
			collapse (sum) `var', by(year fips) 
			
			save "data/BEA/temp_`ind'`var'_1969_2018.dta",replace
			
		}
		
		qui use "data/BEA/temp_`ind'emp_1969_2018.dta", clear
		qui merge 1:1 fips year using "data/BEA/temp_`ind'pay_1969_2018.dta"
		qui g industry = "`ind'"
		keeporder ind* year fips pay emp 
		sort year fips
		qui save "data/BEA/temp_`ind'_1969_2018.dta",replace
		rm "data/BEA/temp_`ind'emp_1969_2018.dta"
		rm "data/BEA/temp_`ind'pay_1969_2018.dta"
		
		}
		
	** Combine All Industries
		
		clear all
		foreach ind of newlist nonfarm mfg mining transutil {
		
			append using "data/BEA/temp_`ind'_1969_2018.dta"
			
			}
		
		qui replace pay = pay*1000
		
	** Keep Common Counties

		preserve
		import delimited "data/tradecosts/Fips.csv",clear
		rename v1 fips
		qui save "temp/fips.dta",replace
		restore
		joinby fips using "temp/fips.dta", unmatched(both)

		qui keep if _merge==3
		drop _merge
		rm "temp/fips.dta"
	
	** Create "Everything Else" Industry Group
	
		qui g temp_pay = pay if industry!="nonfarm"
		qui g temp_emp = emp if industry!="nonfarm"
		egen total_pay = total(temp_pay), by(fips year)
		egen total_emp = total(temp_emp), by(fips year)
		qui replace pay = pay - total_pay if industry=="nonfarm"
		qui replace emp = emp - total_emp if industry=="nonfarm"
		qui replace industry="Remaining Industries" if industry=="nonfarm"
		qui replace industry="Manufacturing" if industry=="mfg"
		qui replace industry="Mining" if industry=="mining"
		qui replace industry="Transportation, Utilities, and Information" if industry=="transutil"
		
	** Save Balanced Panel of Counties
	
		sort industry year fips
		keeporder industry year fips pay emp
		label var industry "Industry"
		label var year "Year"
		label var fips "County FIPS Code"
		label var pay "County Payroll"
		label var emp "County Employment"
		compress
		qui save "data/BEA/long_1969_2018.dta",replace
